########################################################################
#  Pyfm - personal finance data-base
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert Citi N.A. (Citibank North America)  Portfolio (investment) Account Positions Detail CSV file to flat lines.
"""
from csv2flt import *
from obj.flat import stagvalue,ptagvalue,pacctid,psecid
import re
import os
from obj import dt

def timere(name):
	return '((?P<%s_hour>\d\d):(?P<%s_min>\d\d)(?P<%s_ampm>(am|pm)))'%(name,name,name)

htf_list=[
#version 0
([
['Description', 'Cusip#', 'Quantity', 'NAV/Price', 'Change', 'Price As of', 'Value', 'Account#']
],
[
	strre('SECNAME'),
	'#(?P<SECID>\d{9})000',
	floatre('UNITS'),
	pvaluere('UNITPRICE'),
	'(?P<CHANGE>[\-\+]\d+\.\d{2}|0)',
	'((?P<month>\d{1,2})/(?P<day>\d\d)/(?P<year>\d{2}))|((?P<hour>\d{1,2}):(?P<min>\d\d)(?P<ampm>(am|pm)))',
	pvaluere('MKTVAL',1),
	'(?P<ACCTID>[\d\-A-Z]+)'],
[
	['Cash Account Balance', ' ', ' ', ' ', ' ', ' ', commanumre('UNITS'), ' '],
	['Total\*', ' ', ' ', ' ', ' ', ' ', valuere('TOTAL',1), ' '],
	['(?P<hour>\d{2}):(?P<min>\d{2}):(?P<sec>\d{2}) (?P<ampm>(AM|PM)) [A-Z][a-z]{2} (?P<day>\d{2}) (?P<month>[A-Z][a-z]*) (?P<year>\d{4}) ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']
	]),
#version 1 from 6/2007
([
['Description \(SYM\)', 'Cusip#', 'Quantity', 'NAV/Price', 'Change', 'Price As of', 'Value', 'Account#']
],
[
	'(?P<SECNAME>.+)\((?P<TICKER>[A-Z]+)\)\Z',
	'#(?P<SECID>\d{9})000',
	floatre('UNITS'),
	pvaluere('UNITPRICE'),
	'(?P<CHANGE>[\-\+]\d+\.\d{2}|0)',
	'((?P<month>\d{1,2})/(?P<day>\d\d)/(?P<year>\d{2}))|((?P<hour>\d{1,2}):(?P<min>\d\d)(?P<ampm>(am|pm)))',
	pvaluere('MKTVAL',1),
	'(?P<ACCTID>[\d\-A-Z]+)'],
[
	['Cash Account Balance', ' ', ' ', ' ', ' ', ' ', commanumre('UNITS'), ' '],
	['Total\*', ' ', ' ', ' ', ' ', ' ', valuere('TOTAL',1), ' '],
	['(?P<hour>\d{2}):(?P<min>\d{2}):(?P<sec>\d{2}) (?P<ampm>(AM|PM)) [A-Z][a-z]{2} (?P<day>\d{2}) (?P<month>[A-Z][a-z]*) (?P<year>\d{4}) ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']
	]),
#version 2 from 9/2007
([
['Description', 'Cusip#', 'Quantity', 'NAV/Price', 'Change', 'Price As of', 'Value', 'Account#','Symbol']
],
[
	strre('SECNAME'),
	'#(?P<SECID>\d{9})000',
	floatre('UNITS'),
	pvaluere('UNITPRICE'),
	'(?P<CHANGE>[\-\+]\d+\.\d{2}|0)',
	'((?P<month>\d{1,2})/(?P<day>\d\d)/(?P<year>\d{2}))|((?P<hour>\d{1,2}):(?P<min>\d\d)(?P<ampm>(am|pm)))',
	pvaluere('MKTVAL',1),
	'(?P<ACCTID>[\d\-A-Z]+)',
	'(?P<TICKER>[A-Z]+)'],
[
	['Cash Account Balance', ' ', ' ', ' ', ' ', ' ', commanumre('UNITS'), ' '],
	['Total\*', ' ', ' ', ' ', ' ', ' ', valuere_optionalcomma('TOTAL',1), ' '],
	['(?P<hour>\d{2}):(?P<min>\d{2}):(?P<sec>\d{2}) (?P<ampm>(AM|PM)) [A-Z][a-z]{2} (?P<day>\d{2}) (?P<month>[A-Z][a-z]*) 0?(?P<year>\d{4}) ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']
	]),
]

def t24(hour,ampm):
	if ampm.upper()=='PM':
		return '%02d'%(int(hour)+12)
	else:
		return hour

def path(line):
	return ['CitiNA','portfolio','position']

pre=None

def flat(csvfile,fout,linetxt):
	h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)		
		
	# Extract from the footer the date in which the CSV file was extracted
	month={'January':'01','February':'02','March':'03','April':'04','May':'05','June':'06','July':'07',
	       'August':'08','September':'09','October':'10','November':'11','December':'12'}[f[2]['month']]
	statement_date=f[2]['year']+month+f[2]['day']
	statement_dtasof=statement_date+t24(f[2]['hour'],f[2]['ampm'])+f[2]['min']+f[2]['sec']

	accttype='INVESTMENT'

	table_total=0
	accounts=[]
	for l in t:
		units=float(l['UNITS'].replace(',',''))
		unitprice=float(l['UNITPRICE'])
		mktval=float(l['MKTVAL'])
		if abs(mktval-units*unitprice)>0.5:
			print mktval,units*unitprice
		table_total+=mktval
		
		if l['year']:
			dtasof = str(dt.expandyear(int(l['year'])))+l['month']+l['day']+'235959'
		else:
			dtasof= statement_date+t24(l['hour'],l['ampm'])+l['min']+'59'
		print >>fout,stagvalue('DTASOF',dtasof),
		secname=l['SECNAME']
		ticker=l.get('TICKER',None) # Only version>0 has TICKER
			
		print >>fout,stagvalue('SECID','CUSIP='+l['SECID']),stagvalue('SECNAME',secname),stagvalue('TICKER',ticker),
		acctid=l['ACCTID']
		accounts.append(acctid)
		print >>fout,stagvalue('ACCTID',acctid),stagvalue('ACCTTYPE','INVESTMENT'),
		print >>fout,stagvalue('UNITS',units),stagvalue('UNITPRICE',unitprice),stagvalue('MKTVAL',mktval),stagvalue('CURRENCY','USD'),
		print >>fout,linetxt
		
	# Extract from the footer information on the cash account used for investment
	print >>fout,stagvalue('DTASOF',statement_dtasof),
	print >>fout,stagvalue('SECID','CURRENT=CASH=USD'),
	# surprisngly the information available is for one cash account even if there are several investment accounts reported in the same CSV file
	# so we will generate an account which cover all the investment accounts being reported.
	acconts=list(set(accounts))
	for acctid in acconts:
		print >>fout,stagvalue('ACCTID','INVCASH'+acctid),	 # The object builder will make all these ACCTID point to the same Account, because they are all in the same line
	cash = float(f[0]['UNITS'].replace(',',''))
	table_total+=cash
	print >>fout,stagvalue('UNITS',cash),stagvalue('CURRENCY','USD'),
	print >>fout,linetxt
	
	total=float(f[1]['TOTAL'].replace(',',''))
	if abs(total-table_total)>0.5:
		print total,table_total

help = """Convert Citi N.A. (Citibank North America)  Portfolio (investment) Account Positions Detail CSV file to flat lines.
The CSV files should be located at $PYFM_DATA/imports/CitiNA/portfolio/position/
and converted to flat file at  $PYFM_DATA/flt/CitiNA/portfolio/position/
conversions are not perfromed if the files already exists
Use -FORCE=1 to force the convertion
"""

ext='*.csv'
